JOIN is used whenever we have to select data from two or more tables. To be able to use JOIN to extract data from we need a relationship between certain columns in the tables.
Table Used in this
Article
Doctors
ID Name
-----------
--------------------
1 Joe
Manners
2 Sue
Tongs
3 Jeff
Spine
4 Mary
Rasch
5 Tom
Thumb
6 Norm
Lobe
Patients
ID Name DocID
-----------
-------------------- -----------
1 Jim
Thick 4
2 Tom
Small 2
3 Al
Downs 4
4 Ann
Hills 1
5 Tim
Burrow 3
6 Jane
Fern 5
7 Sam
Broom 2
8 Gary
Far 1
9 Bill
Out 5
10 Dave
Bell 4
11 Fred
Overs 5
12 Greg
Double 1
13 Bob
Marks 9
INNER JOIN
An INNER JOIN is the most common join operation used in applications, and represents the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.
Example
select d.name as DocName, p.name as PatientName
from Doctors d inner join Patients p on d.ID=p.DocID
OUTER JOIN
There are three types of OUTER JOIN.
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
LEFT OUTER JOIN
The result of a LEFT OUTER JOIN (or simply LEFT JOIN) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on
the right table.
Example
select d.name as DocName, p.name as PatientName
from Doctors d LEFT OUTER JOIN Patients p
on d.ID=p.DocID
A RIGHT OUTER JOIN (or RIGHT JOIN) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table
(A) exists, NULL will appear in columns from A for those records that have no
match in B. A right outer join returns all the values from the right table and
matched values from the left table (NULL in case of no matching join predicate).
Example
select d.name as DocName, p.name as PatientName
from Doctors d RIGHT OUTER JOIN Patients p
on d.ID=p.DocID
FULL OUTER JOIN
A FULL OUTER JOIN combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side
Example
select d.name as DocName, p.name as PatientName
from Doctors d FULL OUTER JOIN Patients p
on d.ID=p.DocID
You can also read these
related post
https://www.mindstick.com/blog/291/join-in-sql-server
https://www.mindstick.com/Articles/450/joins-in-sql-server
Leave Comment
2 Comments